package com.hegongda.dao.impl;

import com.hegongda.bean.*;
import com.hegongda.dao.AdminDao;
import com.hegongda.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;


public class AdminDaoImpl implements AdminDao {
    @Override
    public int delNav(String cid) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql= "delete from category where cid=?";
        return qr.update(sql,cid);
    }

    @Override
    public int addNav(String cid,String cname) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql= "insert category values(?,?)";
        Object[] params={cid,cname};
        return qr.update(sql,params);
    }

    @Override
    public int updNav(String cid,String cname) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql= "update category set cname=? where cid=?";
        return qr.update(sql,cname,cid);
    }

    @Override
    public int addPriduct(Product product) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql= "insert product values(?,?,?,?,?,?,?,?,?,?,?)";
        Object[] params={product.getPid(),product.getPname(),product.getMarket_price(),product.getShop_price(),product.getPimage(),product.getPdate(),product.getIs_hot(),product.getPdesc(),product.getPflag(),product.getIid(),product.getCid()};
        return qr.update(sql,params);
    }

    @Override
    public int delPriduct(String pid,String iid) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql= "delete from product where pid=?";
        qr.update("delete from introduce where iid=?",iid);
        return qr.update(sql,pid);
    }

    @Override
    public List<Product> getPageProduct(int fistnum, int lastnum) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql= "SELECT * FROM product LIMIT ?,?";
        return qr.query(sql,new BeanListHandler<Product>(Product.class),fistnum,lastnum);
    }

    @Override
    public long getProductCount() throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select count(*) from product";
        return (Long)qr.query(sql,new ScalarHandler());
    }

    @Override
    public Product getOneProduct(String pid) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select * from product where pid = ?";
        return qr.query(sql, new BeanHandler<Product>(Product.class), pid);
    }

    @Override
    public int updPFlag(int pflag,String pid) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql= "update product set pflag=? where pid=?";
        return qr.update(sql,pflag,pid);
    }

    @Override
    public List<User> getPageUser(int fistnum, int lastnum) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql= "SELECT * FROM user LIMIT ?,?";
        return qr.query(sql,new BeanListHandler<User>(User.class),fistnum,lastnum);
    }

    @Override
    public long getUserCount() throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select count(*) from user";
        return (Long)qr.query(sql,new ScalarHandler());
    }

    @Override
    public int delUser(String uid) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql= "delete from user where uid=?";
        return qr.update(sql,uid);
    }

    @Override
    public int updUser(User user) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql= "update user set password=?,name=?,email=?,birthday=?,gender=?,state=?,code=?,remark=? where uid=?";
        Object object[]={user.getPassword(),user.getName(),user.getEmail(),user.getBirthday(),user.getGender(),user.getState(),user.getCode(),user.getRemark(),user.getUid()};
        return qr.update(sql,object);
    }

    @Override
    public User getOneUser(String uid) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select * from user where uid=?";
        return qr.query(sql, new BeanHandler<User>(User.class), uid);
    }

    @Override
    public List<Orders> selAllOrder(int limitone, int limittwo) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT * FROM orders ORDER BY ordertime LIMIT ?,?";
        List<Orders> query = qr.query(sql, new BeanListHandler<Orders>(Orders.class),limitone, limittwo);
        for (Orders orders : query) {
            String oid = orders.getOid();
            sql = "SELECT p.pid, p.pname, p.pimage, p.shop_price, o.count, o.subtotal FROM product  p,orderitem o WHERE p.pid = o.pid AND o.oid=?";
            List<OrderItemView> list1 = qr.query(sql, new BeanListHandler<OrderItemView>(OrderItemView.class), oid);
            orders.setOrderViewList(list1);
        }
        return query;
    }

    @Override
    public long selOrdersCount() throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select count(*) from orders";
        return (Long)qr.query(sql,new ScalarHandler());
    }

    @Override
    public int editCourierNum(String oid, String couriernum) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql= "update orders set couriernum=?,state=2 where oid=?";
        return qr.update(sql,couriernum,oid);
    }

    @Override
    public int editCourier(String oid, String couriernum) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql= "update orders set couriernum=? where oid=?";
        return qr.update(sql,couriernum,oid);
    }

    @Override
    public int addintroduce(String iid, String introduce) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql= "insert introduce values(?,?)";
        return qr.update(sql,iid,introduce);
    }
}
